#!/user/bin/env python
# -*- coding: utf-8 -*-

from utils import log

from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import Table, MetaData, Column, Integer, Unicode, UnicodeText, DateTime, desc
from sqlalchemy.sql.sqltypes import TIMESTAMP
from sqlalchemy.sql import func
from data_pool_mysql import engine

Session = sessionmaker()
Session.configure(bind=engine)

""" topic_title_item collection has
    id:             自增id
    topicid:        话题id（int）
    title:          话题标题（String）
    summary:        话题描述（String）
    cityid:         话题城市（int）
    provid:         话题省份（int）
    imgid:          图片id（int）
    ctime:          话题创建时间（datetime）
"""
metadata = MetaData()


class TopicTitleItem(object):
    """docstring for TopicTitleItem"""

    def to_dict(self):
        res = {}
        res['topicid'] = self.topicid
        res['title'] = self.title
        res['summary'] = self.summary
        res['cityid'] = self.cityid
        res['provid'] = self.provid
        res['imgid'] = self.imgid
        res['ctime'] = self.ctime.strftime("%Y-%m-%d %H:%M:%S")\
            if self.ctime else ""
        return res

    def __repr__(self):
        return "%d" % (self.topicid)


topic_title_table = Table(
    'topic_title', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('topicid', Integer, unique=True, nullable=False),
    Column('title', Unicode(150), nullable=False),
    Column('summary', Unicode(191), nullable=False),
    Column('provid', Integer, index=True, nullable=False),
    Column('cityid', Integer, nullable=False),
    Column('imgid', Integer, nullable=False),
    Column('ctime', TIMESTAMP, server_default=func.now())
)

mapper(TopicTitleItem, topic_title_table)


def create_topic_title_table_if_not_exist():
    metadata.create_all(engine)


def add_topic_title_item(p_item):
    item = TopicTitleItem()
    item.topicid = p_item['topicid']
    item.title = p_item['title']
    item.summary = p_item['summary']
    item.cityid = p_item['cityid']
    item.provid = p_item['provid']
    item.imgid = p_item['imgid']
    try:
        session = Session()
        session.add(item)
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()

'''
def get_topic_title_page():
    try:
        session = Session()
        item_list = session.query(TopicTitleItem) \
            .order_by(desc(TopicTitleItem.ctime)).offset(0).limit(20).all()
        res_list = [i.to_dict for i in item_list]
        return res_list
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()
'''

def get_topic_title_by_provid(p_provid, p_begin, p_limit):
    try:
        session = Session()
        item_list = session.query(TopicTitleItem).filter_by(provid=p_provid)\
            .order_by(desc(TopicTitleItem.ctime)).limit(p_limit).offset(p_begin).all()
        res_list = [i.to_dict() for i in item_list]
        return res_list
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_title_by_provid_list(p_provid_list, p_begin, p_limit):
    try:
        session = Session()
        item_list = session.query(TopicTitleItem)\
            .filter(TopicTitleItem.provid.in_(p_provid_list))\
            .order_by(desc(TopicTitleItem.ctime))\
            .limit(p_limit).offset(p_begin).all()
        res_list = [i.to_dict() for i in item_list]
        return res_list
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_title_dict_by_topicid_list(p_topicid_list):
    try:
        session = Session()
        item_list = session.query(TopicTitleItem.topicid, TopicTitleItem)\
            .filter(TopicTitleItem.topicid.in_(p_topicid_list))\
            .all()
        res_dict = {item[0]: item[1].to_dict() for item in item_list}

        return res_dict
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_topic_title_by_topicid(p_topicid):
    try:
        session = Session()
        item = session.query(TopicTitleItem).filter_by(topicid=p_topicid).first()
        res = item.to_dict()
        return res
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()

def get_newest_topicid():
    try:
        session = Session()
        item = session.query(TopicTitleItem).order_by(TopicTitleItem.ctime.desc()).first()
        res = item.to_dict()
        return res['topicid']
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


def get_random_topicid(except_list):
    try:
        session = Session()
        item_query = session.query(TopicTitleItem)
        if except_list:
            item_query = item_query.filter(~TopicTitleItem.topicid.in_(except_list))
        item = item_query.order_by(func.random()).first()
        if item:
            res = item.to_dict()
            return res['topicid']
        else:
            return None
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()

def update_topic_title_by_topicid(p_topicid, p_item):
    try:
        session = Session()
        item = session.query(TopicTitleItem).filter_by(topicid=p_topicid).upate({'topicid': p_item['topicid'],
                                                                          'title': p_item['title'],
                                                                          'summary': p_item['summary'],
                                                                          'cityid': p_item['cityid'],
                                                                          'provid': p_item['provid'],
                                                                          'imgid': p_item['imgid']})
        return item
    except Exception as e:
        log.exp(e)
        session.rollback()
        return None
    finally:
        session.close()


if __name__ == "__main__":
    create_topic_title_table_if_not_exist()
    item = {'topicid': 1,
            'title': 'test',
            'summary': 'test',
            'cityid': 1,
            'provid': 1,
            'imgid': 1}
    add_topic_title_item(item)
